﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Security.Cryptography;
public class ListAndProcessData
{
    
    public ListAndProcessData()
    {

    }
    
    int IdBusCar = 0;
    int idgroup = 0;
    int IDBusType = 0;
    int IdLocation = 0;
    int IdBTD = 1;
    int IDBusTicket = 0;
    int IdCustomer = 0;
    int totalseats = 0;
    string description = null;
    string BusNo = null;
    string SeatsUsed = null;
    string NameType = null;
    string CustomerName = null;
    string StartLocation = null;
    string EndLocation = null;
    string DateStart = null;
    string TimePlan = null;
    int Price = 0;
    string SeatNo = null;
    string Status = null;
    int Amount = 0;
    DataClassesDataContext da = new DataClassesDataContext();
    public List<TravelPlan> getTravelPlan(){
        var TravelPlan = da.TravelPlans.Select(c => c).ToList();
        return TravelPlan;
    }

    public List<BusCar> getBusCar()
    {
        
        var BusCar = da.BusCars.Select(c => c).ToList();
        return BusCar;
    }

    public List<Customer> getAllCustomer()
    {
        var Customer = da.Customers.Select(c => c).ToList();
        return Customer;
    }

    public List<EndLocation> getEndLocation( string slocation) 
    {
        var a = from t in da.TravelPlans
                where t.StartLocation == slocation
                select new
                {
                    
                    t.EndLocation,
                    
                };
        List<EndLocation> list = new List<EndLocation>();
        foreach (var item in a)
        {
          
            EndLocation = item.EndLocation;
            list.Add(new EndLocation( EndLocation));
        }
        return list;
    }

    public List<StartLocation1> getSLocation()
    {
        var a = (from t in da.TravelPlans

                 select new
                 {

                     t.StartLocation,

                 }).Distinct();
        List<StartLocation1> list = new List<StartLocation1>();
        foreach (var item in a)
        {

            StartLocation = item.StartLocation;
            list.Add(new StartLocation1(StartLocation));
        }
        return list;
    }

    public List<BusType> getBusType()
    {
        var BusType = da.BusTypes.Select(c => c).ToList();
        return BusType;
    }

    public List<Bustype1> searchBustype(string type, string price)
    {
        int price1 = 0;
        List<Bustype1> lst = new List<Bustype1>();
        if (type.Length > 0 && price.Length == 0)
        {
            var a = from t in da.BusTypes
                    where t.Name == type
                    select new
                    {
                        t.IDBusType,
                        t.Name,
                        t.Price,
                        t.TotalSeats,
                        t.Description
                    };   
            foreach (var item in a)
            {
                IDBusType = item.IDBusType;
                NameType = item.Name;
                Price = Convert.ToInt32(item.Price);
                totalseats = Convert.ToInt32(item.TotalSeats);
                description = item.Description;
                lst.Add(new Bustype1(IDBusType, NameType, totalseats, Price, description));
            }
        }
        if (type.Length == 0 && price.Length > 0)
        {
            price1 = int.Parse(price);
            var b = from t in da.BusTypes
                    where t.Price == price1
                    select new
                    {
                        t.IDBusType,
                        t.Name,
                        t.Price,
                        t.TotalSeats,
                        t.Description
                    };
            foreach (var item in b)
            {
                IDBusType = item.IDBusType;
                NameType = item.Name;
                Price = Convert.ToInt32(item.Price);
                totalseats = Convert.ToInt32(item.TotalSeats);
                description = item.Description;
                lst.Add(new Bustype1(IDBusType, NameType, totalseats, Price, description));
            }
        }
        if (type.Length > 0 && price.Length > 0)
        {
            price1 = int.Parse(price); 
            var c = from t in da.BusTypes
                    where t.Name.Equals(type) && t.Price == price1
                    select new
                    {
                        t.IDBusType,
                        t.Name,
                        t.Price,
                        t.TotalSeats,
                        t.Description
                    };
            foreach (var item in c)
            {
                IDBusType = item.IDBusType;
                NameType = item.Name;
                Price = Convert.ToInt32(item.Price);
                totalseats = Convert.ToInt32(item.TotalSeats);
                description = item.Description;
                lst.Add(new Bustype1(IDBusType, NameType, totalseats, Price, description));
            }
        }
        return lst;
    }

    public List<BusCarBusTypeTravelPlan> searchbuscar(string busno, string slocation, string elocation) 
    {
        List<BusCarBusTypeTravelPlan> lst = new List<BusCarBusTypeTravelPlan>();

        if (busno.Length > 0 && slocation.Length == 0 && elocation.Length == 0)
        {
            var a = from t in da.TravelPlans
                    from t1 in da.BusCars
                    from t2 in da.BusTypes
                    where t1.BusNo == busno && t1.IDBusType == t2.IDBusType && t1.IdLocation == t.IdLocation
                    select new
                    {
                        t1.IdBusCar,
                        t1.IDBusType,
                        t1.IdLocation,
                        t1.SeatsUsed,
                        t1.BusNo,
                        t2.Name,
                        t.StartLocation,
                        t.EndLocation,
                        t.DateStart,
                        t.TimePlan,

                    };
            foreach (var item in a)
            {
                IdBusCar = item.IdBusCar;
                IDBusType = Convert.ToInt32(item.IDBusType);
                IdLocation = Convert.ToInt32(item.IdLocation);
                BusNo = item.BusNo;
                SeatsUsed = item.SeatsUsed;
                NameType = item.Name;
                StartLocation = item.StartLocation;
                EndLocation = item.EndLocation;
                DateStart = item.DateStart;
                TimePlan = item.TimePlan;
                lst.Add(new BusCarBusTypeTravelPlan(IdBusCar, IDBusType, IdLocation, NameType, BusNo, SeatsUsed, StartLocation, EndLocation, DateStart, TimePlan));
            }
           
        }
        if (busno.Length == 0 && slocation.Length > 0 && elocation.Length == 0)
        {
            var b = from t in da.TravelPlans
                    from t1 in da.BusCars
                    from t2 in da.BusTypes
                    where t.StartLocation == slocation && t1.IDBusType == t2.IDBusType && t1.IdLocation == t.IdLocation
                    select new
                    {
                        t1.IdBusCar,
                        t1.IDBusType,
                        t1.IdLocation,
                        t1.SeatsUsed,
                        t1.BusNo,
                        t2.Name,
                        t.StartLocation,
                        t.EndLocation,
                        t.DateStart,
                        t.TimePlan,

                    };
            foreach (var item in b)
            {
                IdBusCar = item.IdBusCar;
                IDBusType = Convert.ToInt32(item.IDBusType);
                IdLocation = Convert.ToInt32(item.IdLocation);
                BusNo = item.BusNo;
                SeatsUsed = item.SeatsUsed;
                NameType = item.Name;
                StartLocation = item.StartLocation;
                EndLocation = item.EndLocation;
                DateStart = item.DateStart;
                TimePlan = item.TimePlan;
                lst.Add(new BusCarBusTypeTravelPlan(IdBusCar, IDBusType, IdLocation, NameType, BusNo, SeatsUsed, StartLocation, EndLocation, DateStart, TimePlan));

            }
        }
        if (busno.Length ==0 && slocation.Length == 0 && elocation.Length > 0)
        {
            var c = from t in da.TravelPlans
                    from t1 in da.BusCars
                    from t2 in da.BusTypes
                    where t.EndLocation == elocation && t1.IDBusType == t2.IDBusType && t1.IdLocation == t.IdLocation
                    select new
                    {
                        t1.IdBusCar,
                        t1.IDBusType,
                        t1.IdLocation,
                        t1.SeatsUsed,
                        t1.BusNo,
                        t2.Name,
                        t.StartLocation,
                        t.EndLocation,
                        t.DateStart,
                        t.TimePlan,

                    };
            foreach (var item in c)
            {
                IdBusCar = item.IdBusCar;
                IDBusType = Convert.ToInt32(item.IDBusType);
                IdLocation = Convert.ToInt32(item.IdLocation);
                BusNo = item.BusNo;
                SeatsUsed = item.SeatsUsed;
                NameType = item.Name;
                StartLocation = item.StartLocation;
                EndLocation = item.EndLocation;
                DateStart = item.DateStart;
                TimePlan = item.TimePlan;
                lst.Add(new BusCarBusTypeTravelPlan(IdBusCar, IDBusType, IdLocation, NameType, BusNo, SeatsUsed, StartLocation, EndLocation, DateStart, TimePlan));
            }
        }
        if (busno.Length > 0 && slocation.Length > 0 && elocation.Length == 0)
        {
            var d = from t in da.TravelPlans
                    from t1 in da.BusCars
                    from t2 in da.BusTypes
                    where t1.BusNo == busno && t.StartLocation == slocation && t1.IDBusType == t2.IDBusType && t1.IdLocation == t.IdLocation
                    select new
                    {
                        t1.IdBusCar,
                        t1.IDBusType,
                        t1.IdLocation,
                        t1.SeatsUsed,
                        t1.BusNo,
                        t2.Name,
                        t.StartLocation,
                        t.EndLocation,
                        t.DateStart,
                        t.TimePlan,

                    };
            foreach (var item in d)
            {
                IdBusCar = item.IdBusCar;
                IDBusType = Convert.ToInt32(item.IDBusType);
                IdLocation = Convert.ToInt32(item.IdLocation);
                BusNo = item.BusNo;
                SeatsUsed = item.SeatsUsed;
                NameType = item.Name;
                StartLocation = item.StartLocation;
                EndLocation = item.EndLocation;
                DateStart = item.DateStart;
                TimePlan = item.TimePlan;
                lst.Add(new BusCarBusTypeTravelPlan(IdBusCar, IDBusType, IdLocation, NameType, BusNo, SeatsUsed, StartLocation, EndLocation, DateStart, TimePlan));
            }
        }
        if (busno.Length > 0 && slocation.Length == 0 && elocation.Length > 0)
        {
            var e = from t in da.TravelPlans
                    from t1 in da.BusCars
                    from t2 in da.BusTypes
                    where t1.BusNo == busno && t.EndLocation == elocation && t1.IDBusType == t2.IDBusType && t1.IdLocation == t.IdLocation
                    select new
                    {
                        t1.IdBusCar,
                        t1.IDBusType,
                        t1.IdLocation,
                        t1.SeatsUsed,
                        t1.BusNo,
                        t2.Name,
                        t.StartLocation,
                        t.EndLocation,
                        t.DateStart,
                        t.TimePlan,

                    };
            foreach (var item in e)
            {
                IdBusCar = item.IdBusCar;
                IDBusType = Convert.ToInt32(item.IDBusType);
                IdLocation = Convert.ToInt32(item.IdLocation);
                BusNo = item.BusNo;
                SeatsUsed = item.SeatsUsed;
                NameType = item.Name;
                StartLocation = item.StartLocation;
                EndLocation = item.EndLocation;
                DateStart = item.DateStart;
                TimePlan = item.TimePlan;
                lst.Add(new BusCarBusTypeTravelPlan(IdBusCar, IDBusType, IdLocation, NameType, BusNo, SeatsUsed, StartLocation, EndLocation, DateStart, TimePlan));
            }
        }
        if (busno.Length == 0 && slocation.Length > 0 && elocation.Length > 0)
        {
            var f = from t in da.TravelPlans
                    from t1 in da.BusCars
                    from t2 in da.BusTypes
                    where t.StartLocation == slocation && t.EndLocation == elocation && t1.IDBusType == t2.IDBusType && t1.IdLocation == t.IdLocation
                    select new
                    {
                        t1.IdBusCar,
                        t1.IDBusType,
                        t1.IdLocation,
                        t1.SeatsUsed,
                        t1.BusNo,
                        t2.Name,
                        t.StartLocation,
                        t.EndLocation,
                        t.DateStart,
                        t.TimePlan,

                    };
            foreach (var item in f)
            {
                IdBusCar = item.IdBusCar;
                IDBusType = Convert.ToInt32(item.IDBusType);
                IdLocation = Convert.ToInt32(item.IdLocation);
                BusNo = item.BusNo;
                SeatsUsed = item.SeatsUsed;
                NameType = item.Name;
                StartLocation = item.StartLocation;
                EndLocation = item.EndLocation;
                DateStart = item.DateStart;
                TimePlan = item.TimePlan;
                lst.Add(new BusCarBusTypeTravelPlan(IdBusCar, IDBusType, IdLocation, NameType, BusNo, SeatsUsed, StartLocation, EndLocation, DateStart, TimePlan));
            }
        }
        if (busno.Length > 0 && slocation.Length > 0 && elocation.Length > 0)
        {
            var g = from t in da.TravelPlans
                    from t1 in da.BusCars
                    from t2 in da.BusTypes
                    where t1.BusNo == busno && t.StartLocation == slocation && t.EndLocation == elocation && t1.IDBusType == t2.IDBusType && t1.IdLocation == t.IdLocation
                    select new
                    {
                        t1.IdBusCar,
                        t1.IDBusType,
                        t1.IdLocation,
                        t1.SeatsUsed,
                        t1.BusNo,
                        t2.Name,
                        t.StartLocation,
                        t.EndLocation,
                        t.DateStart,
                        t.TimePlan,

                    };
            foreach (var item in g)
            {
                IdBusCar = item.IdBusCar;
                IDBusType = Convert.ToInt32(item.IDBusType);
                IdLocation = Convert.ToInt32(item.IdLocation);
                BusNo = item.BusNo;
                SeatsUsed = item.SeatsUsed;
                NameType = item.Name;
                StartLocation = item.StartLocation;
                EndLocation = item.EndLocation;
                DateStart = item.DateStart;
                TimePlan = item.TimePlan;
                lst.Add(new BusCarBusTypeTravelPlan(IdBusCar, IDBusType, IdLocation, NameType, BusNo, SeatsUsed, StartLocation, EndLocation, DateStart, TimePlan));
        
            }
        }
        return lst;
    }

    public List<TravelPlan1> searchtravelplan(string slocation, string elocation, string date)
    {
        List<TravelPlan1> lst = new List<TravelPlan1>();
        if (slocation.Length > 0 && elocation.Length == 0 && date.Length == 0)
        {
            var a = from t in da.TravelPlans
                    where t.StartLocation == slocation
                    select new
                    {
                        t.IdLocation,
                        t.StartLocation,
                        t.EndLocation,
                        t.DateStart,
                        t.TimePlan
                    };
            foreach (var item in a)
            {
                IdLocation = item.IdLocation;
                StartLocation = item.StartLocation;
                EndLocation = item.EndLocation;
                DateStart = item.DateStart;
                TimePlan = item.TimePlan;
                lst.Add(new TravelPlan1(IdLocation, StartLocation, EndLocation, DateStart, TimePlan));
            }

        }
        if (slocation.Length == 0 && elocation.Length > 0 && date.Length == 0)
        {
            var b = from t in da.TravelPlans
                    where t.EndLocation == elocation
                    select new
                    {
                        t.IdLocation,
                        t.StartLocation,
                        t.EndLocation,
                        t.DateStart,
                        t.TimePlan
                    };
            foreach (var item in b)
            {
                IdLocation = item.IdLocation;
                StartLocation = item.StartLocation;
                EndLocation = item.EndLocation;
                DateStart = item.DateStart;
                TimePlan = item.TimePlan;
                lst.Add(new TravelPlan1(IdLocation, StartLocation, EndLocation, DateStart, TimePlan));
            }
        }
        if (slocation.Length == 0 && elocation.Length == 0 && date.Length > 0)
        {
            var c = from t in da.TravelPlans
                    where t.DateStart == date
                    select new
                    {
                        t.IdLocation,
                        t.StartLocation,
                        t.EndLocation,
                        t.DateStart,
                        t.TimePlan
                    };
            foreach (var item in c)
            {
                IdLocation = item.IdLocation;
                StartLocation = item.StartLocation;
                EndLocation = item.EndLocation;
                DateStart = item.DateStart;
                TimePlan = item.TimePlan;
                lst.Add(new TravelPlan1(IdLocation, StartLocation, EndLocation, DateStart, TimePlan));
            }
        }
        if (slocation.Length > 0 && elocation.Length > 0 && date.Length == 0)
        {
            var d = from t in da.TravelPlans
                    where t.StartLocation == slocation && t.EndLocation == elocation
                    select new
                    {
                        t.IdLocation,
                        t.StartLocation,
                        t.EndLocation,
                        t.DateStart,
                        t.TimePlan
                    };
            foreach (var item in d)
            {
                IdLocation = item.IdLocation;
                StartLocation = item.StartLocation;
                EndLocation = item.EndLocation;
                DateStart = item.DateStart;
                TimePlan = item.TimePlan;
                lst.Add(new TravelPlan1(IdLocation, StartLocation, EndLocation, DateStart, TimePlan));
            }
        }
        if (slocation.Length > 0 && elocation.Length == 0 && date.Length > 0)
        {
            var e = from t in da.TravelPlans
                    where t.StartLocation == slocation && t.DateStart == date
                    select new
                    {
                        t.IdLocation,
                        t.StartLocation,
                        t.EndLocation,
                        t.DateStart,
                        t.TimePlan
                    };
            foreach (var item in e)
            {
                IdLocation = item.IdLocation;
                StartLocation = item.StartLocation;
                EndLocation = item.EndLocation;
                DateStart = item.DateStart;
                TimePlan = item.TimePlan;
                lst.Add(new TravelPlan1(IdLocation, StartLocation, EndLocation, DateStart, TimePlan));
            }
        }
        if (slocation.Length == 0 && elocation.Length > 0 && date.Length > 0)
        {
            var f = from t in da.TravelPlans
                    where t.EndLocation == elocation && t.DateStart == date
                    select new 
                    {
                        t.IdLocation,
                        t.StartLocation,
                        t.EndLocation,
                        t.DateStart,
                        t.TimePlan
                    };
            foreach (var item in f)
            {
                IdLocation = item.IdLocation;
                StartLocation = item.StartLocation;
                EndLocation = item.EndLocation;
                DateStart = item.DateStart;
                TimePlan = item.TimePlan;
                lst.Add(new TravelPlan1(IdLocation, StartLocation, EndLocation, DateStart, TimePlan));
            }
        }
        if (slocation.Length > 0 && elocation.Length > 0 && date.Length > 0)
        {
            var g = from t in da.TravelPlans
                    where t.EndLocation == elocation && t.DateStart == date && t.StartLocation == slocation
                    select new
                    {
                        t.IdLocation,
                        t.StartLocation,
                        t.EndLocation,
                        t.DateStart,
                        t.TimePlan
                    };
            foreach (var item in g)
            {
                IdLocation = item.IdLocation;
                StartLocation = item.StartLocation;
                EndLocation = item.EndLocation;
                DateStart = item.DateStart;
                TimePlan = item.TimePlan;
                lst.Add(new TravelPlan1(IdLocation, StartLocation, EndLocation, DateStart, TimePlan));
            }
        }
        return lst;
    }

    public BusTicketDetail getBusTicketDetail(string id)
    {
        int idticket = int.Parse(id);
        var BusTicketDetail = da.BusTicketDetails.SingleOrDefault(c => c.IDBusTicket == idticket);
        return BusTicketDetail;
    }

    public Boolean InsertTravelPlan(string SLocation, string ELocation, string DateStart, string TimePlan)
    {
        try
        {
            TravelPlan travel = new TravelPlan();
            travel.StartLocation = SLocation;
            travel.EndLocation = ELocation;
            travel.DateStart = DateStart;
            travel.TimePlan = TimePlan;
            da.TravelPlans.InsertOnSubmit(travel);
            da.SubmitChanges();
            return true;
        }
        catch (Exception)
        {
            return false;
            
        }
        

    }

    public Boolean UpdateTravelPlan(string id,string SLocation, string ELocation, string DateStart, string TimePlan)
    {
        
     try
        {
            TravelPlan travel = da.TravelPlans.Single(c => c.IdLocation == int.Parse(id));
            travel.StartLocation = SLocation;
            travel.EndLocation = ELocation;
            travel.DateStart = DateStart;
            travel.TimePlan = TimePlan;
            
            da.SubmitChanges();
            return true;
        }
        catch (Exception)
        {
            return false;
            
        }


    }

    public Boolean InsertBusCar(string Busno, string Seatuse, string SLocation, string BusType, string Elocation)
    {
        try
        {
            var a = from t in da.TravelPlans
                    from t1 in da.BusTypes
                    where SLocation == t.StartLocation && BusType == t1.Name && t.EndLocation == Elocation
                    select new
                    {
                        t.IdLocation,
                        t1.IDBusType
                    };
            foreach (var item in a)
            {
                IDBusType = item.IDBusType;
                IdLocation = item.IdLocation;
            }
            BusCar car = new BusCar();
            car.IDBusType = IDBusType;
            car.IdLocation = IdLocation;
            car.BusNo = Busno;
            car.SeatsUsed = Seatuse;
            da.BusCars.InsertOnSubmit(car);
            da.SubmitChanges();
            return true;
        }
        catch (Exception)
        {
            return false;

        }

    }

    public Boolean UpdateBusCar(int id, string Busno, string Seatuse, string SLocation, string BusType,string Elocation)
    {
        
        try
        {
            var a = from t in da.TravelPlans
                    from t1 in da.BusTypes
                    where SLocation == t.StartLocation && BusType == t1.Name && Elocation == t.EndLocation
                    select new
                    {
                        t.IdLocation,
                        t1.IDBusType
                    };
            foreach (var item in a)
            {
                IDBusType = item.IDBusType;
                IdLocation = item.IdLocation;
            }
            BusCar car = da.BusCars.Single(c =>c.IdBusCar == id);
            car.IDBusType = IDBusType;
            car.IdLocation = IdLocation;
            car.BusNo = Busno;
            car.SeatsUsed = Seatuse;

            da.SubmitChanges();
            return true;
        }
        catch (Exception)
        {
            return false;

        }



    }

    public Boolean InsertBusType(string Name, string TotalSeat, string Price, string Des)
    {
        try
        {
            BusType t = new BusType();
            t.Name = Name;
            t.TotalSeats = int.Parse(TotalSeat);
            t.Price = int.Parse(Price);
            t.Description = Des;
            da.BusTypes.InsertOnSubmit(t);
            da.SubmitChanges();
         

            return true;
        }
        catch (Exception)
        {
            return false;

        }

    }

    public Boolean UpdateBusType(string id, string Name, string TotalSeat, string Price, string Des)
    {
        

        try
        {
            BusType t = da.BusTypes.Single(c => c.IDBusType == int.Parse(id));
            t.Name = Name;
            t.TotalSeats = int.Parse(TotalSeat);
            t.Price = int.Parse(Price);
            t.Description = Des;
           
            da.SubmitChanges();


            return true;
        }
        catch (Exception)
        {
            return false;

        }

    }

    public Boolean InsertTicketDetail(int Price, string SeatNo, string cusname, string busno)
    {
        try
        {
            var a = from t1 in da.BusCars
                    from t3 in da.Customers
                    where t3.FullName.Equals(cusname) && t1.BusNo.Equals(busno)
                    select new
                    {
                        t3.IdCustomer,
                        t1.IdBusCar
                    };
            foreach (var item in a)
            {
                IdCustomer = item.IdCustomer;
                IdBusCar = item.IdBusCar;
            };
            var b = from t in da.BusTickets
                    where t.IdCustomer == IdCustomer
                    select new { t.IDBusTicket };
            foreach (var item in b)
            {
                IDBusTicket = item.IDBusTicket;
            }

            BusTicketDetail bt = new BusTicketDetail();
            bt.IdBusCar = IdBusCar;
            bt.IDBusTicket = IDBusTicket;
            bt.IdCustomer = IdCustomer;
            bt.Price = Price;
            bt.SeatNo = SeatNo;
            da.BusTicketDetails.InsertOnSubmit(bt);
            da.SubmitChanges();
            return true;
        }
        catch (Exception)
        {
            return false;

        }

    }

    public Boolean DeleteTravelPlan(int id)
    {

        bool check = false;

        try
        {
            var a = da.TravelPlans.Where(c => c.IdLocation == id).ToList();
            foreach (var del in a)
            {
                var b = da.BusCars.Where(c => c.IdLocation == del.IdLocation).ToList();
                foreach (var item in b)
                {
                    var d = da.BusTicketDetails.Where(c => c.IdBusCar == item.IdBusCar).ToList();
                    foreach (var item1 in d)
                    {
                        da.BusTicketDetails.DeleteOnSubmit(item1);
                    }
                    da.BusCars.DeleteOnSubmit(item);
                }
                da.TravelPlans.DeleteOnSubmit(del);
                da.SubmitChanges();
            }
            check = true;
        }
        catch
        {
            check = false;
        }
        
        return check;
    }

    public Boolean DeleteBusCar(string id)
    {
        bool check = false;

        try
        {
            var a = da.BusCars.Where(c => c.IdBusCar == int.Parse(id)).ToList();
            foreach (var del in a)
            {
                var b = da.BusTicketDetails.Where(c => c.IdBusCar == del.IdBusCar).ToList();
                foreach (var item in b)
                {
                    da.BusTicketDetails.DeleteOnSubmit(item);
                }
                da.BusCars.DeleteOnSubmit(del);

            }
            da.SubmitChanges();
            check = true;
        }
        catch
        {
            check = false;
        }
       
        return check;
    }

    public Boolean DeleteBusType(string id)
    {
        bool check = false;
        try
        {

            var a = da.BusTypes.Where(c => c.IDBusType == int.Parse(id)).ToList();
            foreach (var del in a)
            {
                var b = da.BusCars.Where(c => c.IDBusType == del.IDBusType).ToList();
                foreach (var item in b)
                {
                    da.BusCars.DeleteOnSubmit(item);
                }
                da.BusTypes.DeleteOnSubmit(del);

            }
            da.SubmitChanges();
            check = true;
        }
        catch
        {
            check = false;
        }

        return check;
    }

    public Boolean DeleteBusTicketDetail(string id)
    {
        bool check = false;

        try
        {
            var a = da.BusTicketDetails.Where(c => c.IdBTD == int.Parse(id)).ToList();
            foreach (var del in a)
            {
               
                da.BusTicketDetails.DeleteOnSubmit(del);

            }
            check = true;
        }
        catch
        {
            check = false;
        }
        da.SubmitChanges();
        return check;
    }

    public List<BusCarBusTypeTravelPlan> getAllBuscartypeplan()
    {
        var a = from t in da.TravelPlans
                from t1 in da.BusCars
                from t2 in da.BusTypes
                where t1.IDBusType == t2.IDBusType && t1.IdLocation == t.IdLocation
                select new
                {
                    t1.IdBusCar,
                    t1.IDBusType,
                    t1.IdLocation,
                    t1.SeatsUsed,
                    t1.BusNo,
                    t2.Name,
                    t.StartLocation,
                    t.EndLocation,
                    t.DateStart,
                    t.TimePlan,

                };
        List<BusCarBusTypeTravelPlan> list = new List<BusCarBusTypeTravelPlan>();
        foreach (var item in a)
	    {
            IdBusCar = item.IdBusCar;
            IDBusType = Convert.ToInt32(item.IDBusType);
            IdLocation = Convert.ToInt32(item.IdLocation);
		    BusNo = item.BusNo;
            SeatsUsed = item.SeatsUsed;
            NameType = item.Name;
            StartLocation = item.StartLocation;
            EndLocation = item.EndLocation;
            DateStart = item.DateStart;
            TimePlan = item.TimePlan;
            list.Add(new BusCarBusTypeTravelPlan(IdBusCar, IDBusType, IdLocation,NameType ,BusNo, SeatsUsed, StartLocation, EndLocation, DateStart, TimePlan));
	    }
        return list;
    }

    public List<ShowTicketDetail> getAllShowTicketDetail(int id)
    {
        var a = from t in da.BusTicketDetails
                from t1 in da.BusTickets
                from t2 in da.BusCars
                from t3 in da.Customers
                where id == 1 && t.IdBusCar == t2.IdBusCar && t.IdCustomer == t3.IdCustomer && t.IDBusTicket == t1.IDBusTicket
                select new
                {
                    t.IdBTD,
                    t.IdBusCar,
                    t.IDBusTicket,
                    t.IdCustomer,
                    t.SeatNo,
                    t1.Amount,
                    t1.Status,
                    t.Price,
                    t2.BusNo,
                    t3.FullName,
                };
        List<ShowTicketDetail> list = new List<ShowTicketDetail>();
        foreach (var item in a)
        {
            IdBTD = item.IdBTD;
            IdCustomer = Convert.ToInt32(item.IdCustomer);
            IDBusTicket = Convert.ToInt32(item.IDBusTicket);
            IdBusCar = Convert.ToInt32(item.IdBusCar);
            Price = Convert.ToInt32(item.Price);
            SeatNo = item.SeatNo;
            BusNo = item.BusNo; 
            CustomerName = item.FullName;
            if (Convert.ToInt32(item.Status) == 0)
            {
                Status = "";
            }
            else
            {
                Status = "";
            }
            Amount = Convert.ToInt32(item.Amount);
            list.Add(new ShowTicketDetail(IdBTD, IDBusTicket, IdBusCar, IdCustomer, CustomerName, BusNo, SeatNo, Price));
        }
        return list;
    }

    public List<Employee> Login(string user, string pass)
    {
        byte[] mang = System.Text.Encoding.UTF8.GetBytes(pass);

        MD5CryptoServiceProvider my_md5 = new MD5CryptoServiceProvider();
        mang = my_md5.ComputeHash(mang);
        string password1 = null;
        foreach (byte c in mang)
        {
            password1 = c.ToString();
        }
        List<Employee> lst = new List<Employee>();
        var b = da.Employees.SingleOrDefault(a => a.LoginName == user && a.Password == password1);
        
        lst.Add(b);

        return lst;
      
        
        
    }
  
}
